﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EmoneyWeiXinCommon;
using EmoneyWeiXinEntity;
using System.Data;

namespace EmoneyWeiXinThreadDAL
{
    public class newsReportDAL
    {
        public List<NewsReportEntity> getNewsReportUpDataAll(string updatetime)
        {
            string sql = "SELECT ID, weixinid, MemuType, title, Author, pubDate, Stock, keyword, Summary, Content, PubStatus, Step, OutsideUrl, ImageUrl, Nature, isupdate, isvalid, createtime, updatetime, remark, newguid, PubMethod, source FROM dbo.newsReport with(nolock) WHERE updatetime>'" + updatetime + "' and  PubStatus in(1,2) and isvalid=1 ORDER BY CONVERT(VARCHAR(10), pubDate,120) desc, updatetime DESC";
            return SQLHelper.GetDataByColumnsLst<NewsReportEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public List<NewsReportEntity> getNewsReportUpDataAllLM()
        {
            string sql = "SELECT * FROM (SELECT ID, weixinid, MemuType, title, Author, pubDate, Stock, keyword, Summary, Content, PubStatus, Step, OutsideUrl, ImageUrl, Nature, isupdate, isvalid, createtime, updatetime, remark, newguid, PubMethod, source,RANK() OVER (PARTITION BY MemuType,weixinid ORDER BY pubDate desc) gindex FROM dbo.newsReport WHERE isvalid=1 AND PubStatus IN (3,2) AND MemuType<>'') T1 WHERE gindex=1  ORDER BY T1.pubDate DESC";
            return SQLHelper.GetDataByColumnsLst<NewsReportEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public void newsReportUpdatefbzt(string newsreportid)
        {
            string sql = "update newsReport set PubStatus=2 where ID=" + newsreportid;
            SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, null);
        }

        public List<ReplyManagerEntity> getReplyManagerEntityAll()
        {
            string sql = "SELECT * FROM (SELECT id, weixinid, hisid, CONTENT, createtime, updatetime, remark, isvalid,RANK() OVER (PARTITION BY weixinid,hisid ORDER BY updatetime desc) gindex FROM ReplyManager WITH(NOLOCK)) T1 WHERE gindex=1";
            return SQLHelper.GetDataByColumnsLst<ReplyManagerEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public List<KeyWordManagerEntity> GetKeyWordManagerEntityAll()
        {
            string sql = "select * from (select id, keyword, replyContent, isvalid, isdel, validStartTime, validEndTime, isLongValid, createtime, updatetime, weixinid,RANK() over (PARTITION BY keyword order by updateTime desc) gindex from KeyWordManager with(nolock)) tb where gindex=1";
            return SQLHelper.GetDataByColumnsLst<KeyWordManagerEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public NewsReportEntity GetNewsReportById(int ID)
        {
            string sql = "select *from newsReport where ID='" + ID + "'";
            return SQLHelper.GetDataByColumns<NewsReportEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public List<KeyWordManager_NewsReportEntity> GetKeyWordManager_NewsReport()
        {
            string sql = @"select distinct T1.weixinid,T1.keyword,T2.newsreport_id,OutsideUrl,Summary,ImageUrl,title from KeyWordManager T1 with(nolock)
                           INNER JOIN KeyWordManager_NewsReport T2 with(nolock)  ON  T1.id=T2.keyword_id 
                           INNER JOIN newsReport T3 with(nolock) ON T2.newsreport_id=T3.ID
                           where t1.isdel!=1 AND t1.isvalid=1";
            return SQLHelper.GetDataByColumnsLst<KeyWordManager_NewsReportEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public List<ReplyManager_NewsReportEntity1> GetReplyManager_NewsReport()
        {
            string sql = "SELECT replyid,newsreport_id,t2.weixinid,t2.MemuType,t2.Summary,t2.ImageUrl,t2.title,t2.OutsideUrl FROM(SELECT replyid,newsreport_id,RANK() OVER(PARTITION BY replyid,newsreport_id ORDER BY updatetime desc) gindex FROM [ReplyManager_NewsReport] WITH(nolock) ) t1 INNER JOIN dbo.newsReport t2 WITH(NOLOCK) ON t1.newsreport_id=t2.ID WHERE t1.gindex=1 AND t2.isvalid=1 AND t2.PubStatus=3 ";
            return SQLHelper.GetDataByColumnsLst<ReplyManager_NewsReportEntity1>(Config.EmoneyWeiXin, sql, null);
        }

        public List<MenuReportMergeEntity> GetMenuReportMergeData()
        {
            string sql = @"select t1.weixinId,t1.menuType,t1.newsReportId,t1.ID,t2.title,t2.OutsideUrl,t2.Summary,t2.ImageUrl from MenuReportMerge t1 with(nolock)
                           LEFT JOIN newsReport t2 with(nolock)
                           ON t1.newsReportId=t2.ID";
            return SQLHelper.GetDataByColumnsLst<MenuReportMergeEntity>(Config.EmoneyWeiXin, sql, null);
        }

        public List<string> GetHotsZTCode()
        {
            string sql = "SELECT top 8 ZTCode FROM ZTHotsRecord with(nolock) ORDER BY clickCount DESC";
            DataTable dt = SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, null);
            List<string> lst = new List<string>();
            foreach (DataRow item in dt.Rows)
            {
                lst.Add(item[0].ToString());
            }
            return lst;
        }
    }
}
